﻿'
'The Following codes copyed from:
'  http://topic.csdn.net/u/20071217/14/A87C8FAE-AB74-4D2D-8A19-B1E13A2FDEEE.html
'

Imports System.IO
Imports System.Data
Imports System.Data.OleDb

Public Class DataAccess

    Private adapter As OleDbDataAdapter
    Private conn As OleDbConnection

    Private isInitialized As Boolean = False

    Private Sub Initial()

        If isInitialized = False Then
            Dim DataPath As String           '数据库连接的路径
            Dim UserID As String             '数据库连接的用户名
            Dim UserPwd As String            '数据库连接的密码

            DataPath = "商品进销存.accdb"
            UserID = "user"
            UserPwd = "password"

            Dim connString As String

            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DataPath
            conn = New OleDbConnection(connString)

            adapter = New OleDbDataAdapter
            adapter.SelectCommand = New OleDbCommand("", conn)

            isInitialized = True
        End If
    End Sub

    'Open database connection.
    Public Function OpenConnection() As Boolean
        Initial()
        If conn.State = ConnectionState.Closed Then
            Try
                conn.Open()
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
                Return False
            End Try
        End If
        Return True
    End Function

    'Close database connection.
    Public Function CloseConnection() As Boolean
        If conn.State = ConnectionState.Open Then
            Try
                conn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
                Return False
            End Try
        End If
        Return True
    End Function

    'Update database ON SQL
    Public Function UpdateDatabase(ByVal sqlText As String) As Boolean
        Try
            adapter.SelectCommand.CommandText = sqlText
            adapter.SelectCommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return False
        Finally
            ' CloseConnection()
        End Try
        Return True
    End Function

    'Update database ON Table
    Public Function UpdateDatabase(ByVal dt As DataTable) As Boolean
        Try
            Dim oCB As New OleDbCommandBuilder(adapter)
            'adapter.Fill(dt)
            adapter.Update(dt)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return False
        Finally
            ' CloseConnection()
        End Try
        Return True
    End Function

    'Return Data in DataSet on SQL and TableName
    Public Function QueryDatabase(ByVal sqlText As String) As DataTable
        Dim ds As New DataSet
        Try
            adapter.SelectCommand.CommandText = sqlText
            adapter.Fill(ds, "DATA_TABLE")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        Return ds.Tables("DATA_TABLE")
    End Function

    Public Sub SetSelectCommand(ByVal sqlText As String)
        adapter.SelectCommand.CommandText = sqlText
    End Sub

    '
    '下面是未使用的代码
    Public Function BeginTransaction() As OleDb.OleDbTransaction
        Dim tx As OleDb.OleDbTransaction
        Me.OpenConnection()
        tx = conn.BeginTransaction
        adapter.SelectCommand.Transaction = tx
        Return tx
    End Function

    Public Function GET_COLUMN_MSG(ByVal v_table_name As String, ByVal v_num As String, ByVal v_type As String) As String
        OpenConnection()
        Dim adjCmd As New OleDbCommand
        Dim v_owner As String = "ARM"
        Dim strSQL As String
        Dim v_column_name As String

        adjCmd.Connection = conn
        Select Case v_type
            Case "TITLE"   '表示取的是抬头
                strSQL = "SELECT COLUMN_NAME FROM " & _
                          " ( select TO_CHAR( ROWNUM )   NUM,COLUMN_NAME   FROM ALL_TAB_COLUMNS " & _
                          "   WHERE OWNER ='" & v_owner & "' AND TABLE_NAME = '" & v_table_name & "')" & _
                          "   WHERE (NUM =" & v_num & ")"
                adjCmd.CommandText = strSQL
                v_column_name = adjCmd.ExecuteScalar().ToString()
                strSQL = " Select SUBSTR(COMMENTS, 1, 20) FROM ALL_COL_COMMENTS " & _
                          " WHERE   OWNER='" & v_owner & "' AND TABLE_NAME='" & v_table_name & "' AND " & _
                          " COLUMN_NAME = '" & v_column_name & "'"
            Case "LEN"   ' 表示取的长度 
                strSQL = "SELECT DATA_LENGTH FROM " & _
                          " ( select TO_CHAR( ROWNUM )   NUM,DATA_LENGTH   FROM ALL_TAB_COLUMNS " & _
                          " WHERE OWNER ='" & v_owner & "' AND TABLE_NAME ='" & v_table_name & "')" & _
                          " WHERE NUM=" & v_num
            Case "NAME"   ' 表示取的是名字
                strSQL = "SELECT COLUMN_NAME FROM " & _
                          " ( select TO_CHAR( ROWNUM )   NUM,COLUMN_NAME   FROM ALL_TAB_COLUMNS " & _
                          "   WHERE OWNER ='" & v_owner & "' AND TABLE_NAME = '" & v_table_name & "')" & _
                          "   WHERE (NUM =" & v_num & ")"
        End Select

        Return CStr(adjCmd.ExecuteScalar)
    End Function
    Public Function GetCmd(ByVal sqlText As String) As OleDbCommand
        Dim cmd As New OleDbCommand(sqlText, conn)
        Return cmd
    End Function

    Public Function GetOnceValue(ByVal sqlText As String) As String
        Try
            adapter.SelectCommand.CommandText = sqlText
            Dim oValue As Object = adapter.SelectCommand.ExecuteScalar
            If oValue Is DBNull.Value Or oValue Is Nothing Then
                Return ""
            Else
                Return CType(oValue, String)
            End If

        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Function

    'GetLoginPersonNo: 根據登陸號獲取用戶ID
    '輸入參數：
    '        seq：     用戶登陸號
    '返回值：String            用戶ID
    Public Function GetLoginPersonNo(ByVal seq As String) As String

        Dim ds As DataTable
        ds = QueryDatabase("select USERID from login where LOGIN_NO='" & seq & "'")

        If ds.Rows.Count > 0 Then
            Return CStr(ds.Rows(0)(0))
        Else
            Return ""
        End If

    End Function

    'Public Function GetPersonName(ByVal USERID As String) As String
    '    Dim ds As DataTable
    '    Return Me.GetOnceValue("SELECT PERSON_NAME FROM PERSON WHERE PERSON_NO='" & USERID & "'")
    'End Function

    'Public Function GetGRPName(ByVal USERID As String) As String

    '    Dim ds As DataTable
    '    Return Me.GetOnceValue("SELECT G.GRP_NAME FROM GRP G,PERSON_GRP PG WHERE PG.PERSON_NO='" & USERID & "' AND G.GRP_NO=PG.GRP_NO")
    'End Function

    Public Function IsAqua(ByVal personNo As String) As Boolean
        Dim strSql As String = "SELECT NVL(COUNT(*),0) FROM PERSON_GRP WHERE GRP_NO IN ('000006','000009') AND PERSON_NO='" & personNo & "'"
        Return Int32.Parse(GetOnceValue(strSql)) > 0
    End Function
End Class
